﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using TimeTableManagement.Entities;
using System.Data.OleDb;
using System.Data;

namespace TimeTableManagement.Controllers
{
    [Authorize]
    public class UsersController : Controller
    {
        QLSVContext _context = new QLSVContext();
        //Kiem tra quyen cua user
        private Boolean checkPermission(String action)
        {
            if (Session["USERNAME"] == null)
                Redirect("/Account/LogOn");
            String username = (String)Session["USERNAME"];
            String module = "sys";
            Boolean per = new Tb_Role().getPermission(username, module, action);
            if (per) return true;
            else return false;
        }

        //
        // GET: /Management/
        // Danh sach user dang online
        // Nhat ky su dung
        public ActionResult Index()
        {
            //if (!checkPermission("view")) return RedirectToAction("Index", "Home");
            var model = _context.Tb_Userlogin.Include("Tb_Role").ToList();
            return View(model);
        }
        
        //Them nguoi dung
        public ActionResult CreateSingleUser()
        {
           // if (!checkPermission("create")) return RedirectToAction("Index", "Home");
            ViewBag.id_role = new SelectList(_context.Tb_Role, "id", "rolename");
            return View();
        }
        [HttpPost]
        public ActionResult CreateSingleUser(Tb_Userlogin model)
        {
           // if (!checkPermission("create")) return RedirectToAction("Index", "Home");
            if (ModelState.IsValid)
            {
                model.password = TimeTableManagement.Models.Functions.Encrypt(model.password);
                _context.Tb_Userlogin.Add(model);
                _context.SaveChanges();
            }
            return RedirectToAction("UserManager");
        }

        public ActionResult RoleManager()
        {
            //if (!checkPermission("view")) return RedirectToAction("Index", "Home");
            var model = _context.Tb_Role.ToList();
            return View(model);
        }
        public ActionResult RoleDetail(int id)
        {
            var model = _context.Tb_Role.Find(id);
            return View(model);
        }
        public ActionResult AddRole()
        {
            return View();
        }
        [HttpPost]
        public ActionResult AddRole(Tb_Role role)
        {
            if (ModelState.IsValid)
            {
                _context.Tb_Role.Add(role);
                _context.SaveChanges();
                return RedirectToAction("RoleManager");
            }
            return View();
        }
        public ActionResult CreateMultiUser(string filename)
        {
            if (filename == null)
            {
                ViewData["kq"] = "no";
                return View();
            }
            else
            {
                int i = this.insertUser(filename);
                if (i == 0) ViewData["kq"] = "ok";
                else ViewData["kq"] = "un";
                return View();
            }
        }

        //
        // GET: /Users/Edit/5

        public ActionResult Edit(int id)
        {
            Tb_Userlogin tb_userlogin = _context.Tb_Userlogin.Find(id);
            ViewBag.id_role = new SelectList(_context.Tb_Role, "id", "rolename", tb_userlogin.id_role);
            return View(tb_userlogin);
        }

        //
        // POST: /Users/Edit/5

        [HttpPost]
        public ActionResult Edit(Tb_Userlogin tb_userlogin)
        {
            if (ModelState.IsValid)
            {
                _context.Entry(tb_userlogin).State = EntityState.Modified;
                _context.SaveChanges();
                return RedirectToAction("Index");
            }
            ViewBag.id_role = new SelectList(_context.Tb_Role, "id", "rolename", tb_userlogin.id_role);
            return View(tb_userlogin);
        }

        public ActionResult Delete(int id)
        {
            Tb_Userlogin tb_userlogin = _context.Tb_Userlogin.Find(id);
            return View(tb_userlogin);
        }

        //
        // POST: /Users/Delete/5

        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(int id)
        {
            Tb_Userlogin tb_userlogin = _context.Tb_Userlogin.Find(id);
            _context.Tb_Userlogin.Remove(tb_userlogin);
            _context.SaveChanges();
            return RedirectToAction("Index");
        }

        private List<Tb_Userlogin> getTb_User(String fi)
        {
            List<Tb_Userlogin> list = new List<Tb_Userlogin>();

            //string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="
            //    + Server.MapPath("~/Uploads/" + fi) + ";" + "Extended Properties=Excel 8.0; HDR=Yes;IMEX=1";
            //string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/Uploads/" + fi) + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/Uploads/" + fi) + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
            // Create the connection object
            OleDbConnection oledbConn = new OleDbConnection(connString);
            try
            {
                // Open connection
                oledbConn.Open();

                // Create OleDbCommand object and select data from worksheet Sheet1
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);

                // Create new OleDbDataAdapter
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                DataSet ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds);

                int i = 0;
                DataRow r = ds.Tables[0].Rows[0];
                while (r != null)
                {
                    Array t = r.ItemArray;
                    Tb_Userlogin user = new Tb_Userlogin();
                    user.username = (string)t.GetValue(0);
                    user.displayname = (string)t.GetValue(1);
                    user.password = TimeTableManagement.Models.Functions.Encrypt((string)t.GetValue(2));
                    user.email = (string)t.GetValue(3);
                    user.id_role = Int16.Parse((string)t.GetValue(4));
                    list.Add(user);
                    i++;
                    r = ds.Tables[0].Rows[i];
                }
            }
            catch
            {
                //Response.Write(ex);
            }
            finally
            {
                oledbConn.Close();
            }

            return list;
        }
        private int insertUser(String f)
        {
            QLSVContext _context = new QLSVContext();
            List<Tb_Userlogin> list = new List<Tb_Userlogin>();
            list = this.getTb_User(f);
            int len = list.Count;
            int i = 0;
            foreach (Tb_Userlogin u in list)
            {
                _context.Tb_Userlogin.Add(u);
                //_context.SaveChanges();
                i++;
            }
            _context.Dispose();
            return (i - len);
        }
        
    }
}
